/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.catosist.run.dataaccess.domain.hibernate;

import com.catosist.run.dataaccess.base.hibernate.HibernateGenericDAO;
import com.catosist.run.dataaccess.domain.GastoDao;
import com.catosist.run.dataaccess.model.GastoIdVO;
import com.catosist.run.dataaccess.model.GastoVO;
import java.util.LinkedList;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

/**
 *
 * @author Bicho
 */
public class HibernateGastoDao extends HibernateGenericDAO<GastoVO, Integer> implements GastoDao{
    public HibernateGastoDao() {
        super(GastoVO.class);
    }

    @Override
    public List<GastoVO> getByViajeId(int viajeId) {
        DetachedCriteria detachedCriteria = DetachedCriteria.forClass(GastoVO.class);
        detachedCriteria.add(Restrictions.eq("viaje.id", viajeId));
        detachedCriteria.addOrder(Order.desc("fingreso"));
        List<GastoVO> gastoVOs = new LinkedList<GastoVO>();
        try {
            gastoVOs = listByCriteria(detachedCriteria);
        } catch (Exception e) {
        }

        return gastoVOs;
    }

    public List<GastoVO> getAllGroupByDescription() {
        return this.getSession().
                createSQLQuery("SELECT * FROM gasto group by descripcion;").
                addEntity(GastoVO.class).list();
    }

    public List<GastoVO> getAllBoletas() {
        return this.getSession().
                createSQLQuery("select * from gasto where tipo = 'b';").
                addEntity(GastoVO.class).list();
    }

    public List<GastoVO> getAllFacturas() {
        return this.getSession().
                createSQLQuery("select * from gasto where tipo = 'f';").
                addEntity(GastoVO.class).list();
    }

    public List<GastoVO> getAllOtros() {
        return this.getSession().
                createSQLQuery("select * from gasto where tipo = 'o';").
                addEntity(GastoVO.class).list();
    }

    public List<Object> reporteCompras() {
        return this.getSession().
                createSQLQuery("SELECT YEAR(gasto.fingreso) as `anho`, MONTH(gasto.fingreso) as `mes`, SUM(if(gasto.tipo='f' OR gasto.tipo='b',gasto.total,0)) as `sumadeclarada`, SUM(gasto.total) as `sumanodeclarada` FROM gasto GROUP BY YEAR(gasto.fingreso), MONTH(gasto.fingreso)").list();
    }
}
